﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.EducationalProgram {
    public sealed class EducationalPlan : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The EducationalPlan's name field.
        /// </summary>
        private string name;

        /// <summary>
        /// The EducationalPlan's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's name abbreviation field.
        /// </summary>
        private string abbreviation;

        /// <summary>
        /// The EducationalPlan's name abbreviation.
        /// </summary>
        public string Abbreviation {
            get {
                return this.abbreviation;
            }
            set {
                if ( this.abbreviation != value )
                    modifiedDBFields.Add( "abbreviation" );
                this.abbreviation = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's creation year field.
        /// </summary>
        private int year;

        /// <summary>
        /// The EducationalPlan's creation year.
        /// </summary>
        public int Year {
            get {
                return this.year;
            }
            set {
                if ( this.year != value )
                    modifiedDBFields.Add( "year" );
                this.year = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's total credits field.
        /// </summary>
        private int totalCredits;

        /// <summary>
        /// The EducationalPlan's numerical value.
        /// </summary>
        public int TotalCredits {
            get {
                return this.totalCredits;
            }
            set {
                if ( this.totalCredits != value )
                    modifiedDBFields.Add( "total_credits" );
                this.totalCredits = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's duration in terms field.
        /// </summary>
        private int durationInTerms;

        /// <summary>
        /// The EducationalPlan's numerical value.
        /// </summary>
        public int DurationInTerms {
            get {
                return this.durationInTerms;
            }
            set {
                if ( this.durationInTerms != value )
                    modifiedDBFields.Add( "duration_in_terms" );
                this.durationInTerms = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's max duration field.
        /// </summary>
        private int maxDuration;

        /// <summary>
        /// The EducationalPlan's max duration value.
        /// </summary>
        public int MaxDuration {
            get {
                return this.maxDuration;
            }
            set {
                if ( this.maxDuration != value )
                    modifiedDBFields.Add( "max_duration" );
                this.maxDuration = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's minimal duration field.
        /// </summary>
        private int minDuration;

        /// <summary>
        /// The EducationalPlan's minimal duartion value.
        /// </summary>
        public int MinDuration {
            get {
                return this.minDuration;
            }
            set {
                if ( this.minDuration != value )
                    modifiedDBFields.Add( "min_duration" );
                this.minDuration = value;
            }
        }

        /// <summary>
        /// The EducationalPlan's active field.
        /// </summary>
        private bool active;

        /// <summary>
        /// True if <code>this</code> is active, false otherwise.
        /// </summary>
        public bool Active {
            get {
                return this.active;
            }
            set {
                if ( this.active != value )
                    modifiedDBFields.Add( "active" );
                this.active = value;
            }
        }

        /// <summary>
        /// The term field used to divide the educational plan's total time.
        /// </summary>
        private Term term;

        /// <summary>
        /// The term value used to divide the educational plan's total time.
        /// </summary>
        public Term Term {
            get {
                return this.term;
            }
            set {
                if ( !this.term.Equals( value ) )
                    modifiedDBFields.Add( "term_id" );
                this.term = value;
            }
        }

        /// <summary>
        /// The plan's educational level field.
        /// </summary>
        private EducationalLevel educationalLevel;

        /// <summary>
        /// The education level associated to the educational plan.
        /// </summary>
        public EducationalLevel EducationalLevel {
            get {
                return this.educationalLevel;
            }
            set {
                if ( !this.educationalLevel.Equals( value ) )
                    modifiedDBFields.Add( "educational_level_id" );
                this.educationalLevel = value;
            }
        }

        /// <summary>
        /// The plan's academic degree field.
        /// </summary>
        private AcademicDegree academicDegree;

        /// <summary>
        /// The academic degree obtained after completed the educational plan.
        /// </summary>
        public AcademicDegree AcademicDegree {
            get {
                return this.academicDegree;
            }
            set {
                if ( !this.academicDegree.Equals( value ) )
                    modifiedDBFields.Add( "academic_degree_id" );
                this.academicDegree = value;
            }
        }


        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return !( this.name == null ) && ( this.name.Trim( ) != "" )
                    && ( !( this.abbreviation == null ) && ( this.abbreviation.Trim( ) != "" ) )
                    && this.totalCredits >= 0
                    && this.maxDuration >= 1
                    && this.minDuration >= 1
                    && this.durationInTerms > 0
                    && this.year > 0
                    && this.academicDegree.IsValid
                    && this.educationalLevel.IsValid
                    && this.term.IsValid;
            }
        }
        #endregion

        #region Constructors
        public EducationalPlan ( ) {
            this.id = -1;
            this.name = "";
            this.abbreviation = "";
            this.totalCredits = 0;
            this.minDuration = 0;
            this.maxDuration = 0;
            this.durationInTerms = 0;
            this.year = 0;
            this.term = new Term( );
            this.educationalLevel = new EducationalLevel( );
            this.academicDegree = new AcademicDegree( );
            this.active = true;
            this.isNew = true;
        }

        public EducationalPlan ( int id, string name, string abbreviation, int totalCredits, int durationInTerms,
                int minDuration, int maxDuration, int year, Term term, EducationalLevel educationalLevel, AcademicDegree academicDegree, bool active ) {
            this.id = id;
            this.name = name;
            this.abbreviation = abbreviation;
            this.totalCredits = totalCredits;
            this.minDuration = minDuration;
            this.maxDuration = maxDuration;
            this.durationInTerms = durationInTerms;
            this.year = year;
            this.term = term;
            this.educationalLevel = educationalLevel;
            this.academicDegree = academicDegree;
            this.active = active;
            this.isNew = ( id == -1 );
        }

        public EducationalPlan ( string name, string abbreviation, int totalCredits, int durationInTerms,
                int minDuration, int maxDuration, int year, Term term, EducationalLevel educationalLevel, AcademicDegree academicDegree, bool active )
            : this( -1, name, abbreviation, totalCredits, durationInTerms, minDuration, maxDuration, year, term, educationalLevel, academicDegree, active ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            EducationalPlan ep = obj as EducationalPlan;
            if ( ( object ) ep == null ) {
                return false;
            }
            return ( this.name == ep.name )
                && ( this.abbreviation == ep.abbreviation )
                && ( this.active == ep.active )
                && ( this.totalCredits == ep.totalCredits )
                && ( this.maxDuration == ep.maxDuration )
                && ( this.minDuration == ep.minDuration )
                && ( this.durationInTerms == ep.durationInTerms )
                && ( this.year == ep.year )
                && this.educationalLevel.Equals( ep.educationalLevel )
                && this.academicDegree.Equals( ep.academicDegree )
                && this.term.Equals( ep.term );
        }

        /// <summary>
        /// Checks if the EducationalPlan is equal to the current instance.
        /// </summary>
        /// <param name="a">The EducationalPlan to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( EducationalPlan ep ) {
            if ( ( object ) ep == null ) {
                return false;
            }
            return ( this.name == ep.name )
                && ( this.abbreviation == ep.abbreviation )
                && ( this.active == ep.active )
                && ( this.totalCredits == ep.totalCredits )
                && ( this.maxDuration == ep.maxDuration )
                && ( this.minDuration == ep.minDuration )
                && ( this.durationInTerms == ep.durationInTerms )
                && ( this.year == ep.year )
                && this.educationalLevel.Equals( ep.educationalLevel )
                && this.academicDegree.Equals( ep.academicDegree )
                && this.term.Equals( ep.term );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.abbreviation.GetHashCode( )
                ^ this.totalCredits.GetHashCode( )
                ^ this.maxDuration.GetHashCode( )
                ^ this.minDuration.GetHashCode( )
                ^ this.year.GetHashCode( )
                ^ this.durationInTerms.GetHashCode( )
                ^ this.term.GetHashCode( )
                ^ this.academicDegree.GetHashCode( )
                ^ this.educationalLevel.GetHashCode( )
                ^ this.active.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.id, this.abbreviation, this.name, this.totalCredits, 
                this.maxDuration, this.minDuration, this.year,this.term, this.durationInTerms,
                this.educationalLevel, this.academicDegree, this.active };
            bldr.AppendFormat( "{2} ({1}). Total credits: {3}. Max. duration: {4}. Min. duration: {5}. Plan year: {6}."
                + "Term: {7}. Duration in terms: {8}. Level: {9}. Degree: {10}. Active: {11:Yes|No}", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ) {
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                sqlCommand = "SELECT count(*) = 0 as exists FROM educational_plans WHERE id = @id OR name ILIKE @name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                isNew = ( bool ) db.ExecuteScalar( dbCommand );
                if ( isNew ) {
                    sqlCommand = "SELECT nextval('educational_plans_id_seq')";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    this.id = ( int ) db.ExecuteScalar( dbCommand );

                    sqlCommand = "INSERT INTO educational_plans"
                        + " ( id, name, abbreviation, total_credits, max_duration, min_duration, duration_in_terms, year, active,"
                        + " term_id, academic_degree_id, educational_level_id )"
                        + " VALUES"
                        + " ( @id, @name, @abbreviation, @total_credits, @max_duration, @min_duration, @duration_in_terms,"
                        + " @year, @active, @term_id, @academic_degree_id, @educational_level_id )";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@total_credits", DbType.Int32, this.totalCredits );
                    db.AddInParameter( dbCommand, "@max_duration", DbType.Int32, this.maxDuration );
                    db.AddInParameter( dbCommand, "@min_duration", DbType.Int32, this.minDuration );
                    db.AddInParameter( dbCommand, "@duration_in_terms", DbType.Int32, this.durationInTerms );
                    db.AddInParameter( dbCommand, "@year", DbType.Int32, this.year );
                    db.AddInParameter( dbCommand, "@term_id", DbType.Int32, this.term.Id );
                    db.AddInParameter( dbCommand, "@academic_degree_id", DbType.Int32, this.academicDegree.Id );
                    db.AddInParameter( dbCommand, "@educational_level_id", DbType.Int32, this.educationalLevel.Id );
                    db.AddInParameter( dbCommand, "@active", DbType.Boolean, this.active );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else if ( this.modifiedDBFields.Count > 0 ) {
                    sqlCommand = "UPDATE educational_plans SET"
                        + " id = @id, name = @name, abbreviation = @abbreviation, total_credits = @total_credits,"
                        + " max_duration = @max_duration, min_duration = @min_duration, duration_in_terms = @duration_in_terms,"
                        + " year = @year, active = @active, term_id = @term_id, academic_degree_id = @academic_degree_id,"
                        + " educational_level_id = @educational_level_id"
                        + " WHERE id = @id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@total_credits", DbType.Int32, this.totalCredits );
                    db.AddInParameter( dbCommand, "@max_duration", DbType.Int32, this.maxDuration );
                    db.AddInParameter( dbCommand, "@min_duration", DbType.Int32, this.minDuration );
                    db.AddInParameter( dbCommand, "@duration_in_terms", DbType.Int32, this.durationInTerms );
                    db.AddInParameter( dbCommand, "@year", DbType.Int32, this.year );
                    db.AddInParameter( dbCommand, "@term_id", DbType.Int32, this.term.Id );
                    db.AddInParameter( dbCommand, "@academic_degree_id", DbType.Int32, this.academicDegree.Id );
                    db.AddInParameter( dbCommand, "@educational_level_id", DbType.Int32, this.educationalLevel.Id );
                    db.AddInParameter( dbCommand, "@active", DbType.Boolean, this.active );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else {
                    //No update or insert needed
                    saved = true;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the EducationalPlan: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM educational_plans WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the EducationalPlan: " + e.Message );
            }
            return deleted;
        }


        #region Load Methods
        /// <summary>
        /// Loads the EducationalPlan with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The EducationalPlans' id.</param>
        /// <returns>A EducationalPlan object with it's information or a null EducationalPlan object if the <code>id</code> was not present in the database.</returns>
        public static EducationalPlan Load ( int id ) {
            EducationalPlan ep = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT"
                + " ep.id, ep.name, ep.abbreviation, ep.total_credits, ep.max_duration, ep.min_duration, ep.duration_in_terms,"
                + " ep.year, ep.active,"
                + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
                + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
                + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
                + " FROM educational_plans AS ep, terms AS t, time_units AS tu, academic_degrees AS ad, educational_levels AS el"
                + " WHERE ep.id = @id"
                + " t.id = ep.term_id AND t.time_unit_id = tu.id"
                + " el.id = ep.educational_level_id"
                + " ad.id = ep.academic_degree_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        TimeUnit tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        Term t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                        AcademicDegree ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ], ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                        EducationalLevel el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                        ep = new EducationalPlan( id, ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ],
                            ( int ) rdr [ "total_credits" ], ( int ) rdr [ "duration_in_terms" ], ( int ) rdr [ "min_duration" ],
                            ( int ) rdr [ "max_duration" ], ( int ) rdr [ "year" ], t, el, ad, ( bool ) rdr [ "active" ] );
                        ep.modifiedDBFields.Clear( );
                        ep.ClearMessages( );
                        ep.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                ep.errorMessages.Add( string.Format( "There was an error loading the EducationalPlan with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return ep;
        }

        /// <summary>
        /// Loads the EducationalPlan with the given <code>name</code>.
        /// </summary>
        /// <param name="id">The EducationalPlans' name.</param>
        /// <returns>A EducationalPlan object with it's information or a null EducationalPlan object if the <code>name</code> was not present in the database.</returns>
        public static EducationalPlan LoadByName ( string name ) {
            EducationalPlan ep = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT"
                + " ep.id, ep.name, ep.abbreviation, ep.total_credits, ep.max_duration, ep.min_duration, ep.duration_in_terms,"
                + " ep.year, ep.active,"
                + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
                + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
                + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
                + " FROM educational_plans AS ep, terms AS t, time_units AS tu, academic_degrees AS ad, educational_levels AS el"
                + " WHERE ep.name = @name"
                + " t.id = ep.term_id AND t.time_unit_id = tu.id"
                + " el.id = ep.educational_level_id"
                + " ad.id = ep.academic_degree_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        TimeUnit tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                        Term t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                        AcademicDegree ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ], ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                        EducationalLevel el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                        ep = new EducationalPlan( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ],
                            ( int ) rdr [ "total_credits" ], ( int ) rdr [ "duration_in_terms" ], ( int ) rdr [ "min_duration" ],
                            ( int ) rdr [ "max_duration" ], ( int ) rdr [ "year" ], t, el, ad, ( bool ) rdr [ "active" ] );
                        ep.modifiedDBFields.Clear( );
                        ep.ClearMessages( );
                        ep.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                ep.errorMessages.Add( string.Format( "There was an error loading the EducationalPlan {0}: {1}", name, e.Message ) );
            }
            return ep;
        }

        /// <summary>
        /// Checks the database for existence of the EducationalPlan with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The EducationalPlans' id.</param>
        /// <returns><code>True</code> if the grade with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM educational_plans WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for existence of the EducationalPlan with the given <code>name</code>.
        /// </summary>
        /// <param name="name">The EducationalPlans' name</param>
        /// <returns><code>True</code> if the grade with the given <code>name</code>exists, <code>false</code> otherwise.</returns>
        public static bool ExistsByName ( string name ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM educational_plans WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Loads all of the EducationalPlans in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the EducationalPlans on the database.</returns>
        public static List<EducationalPlan> LoadAll ( ) {
            List<EducationalPlan> epl = new List<EducationalPlan>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT"
                 + " ep.id, ep.name, ep.abbreviation, ep.total_credits, ep.max_duration, ep.min_duration, ep.duration_in_terms,"
                 + " ep.year, ep.active,"
                 + " ep.term_id, t.name AS name_t, t.duration AS duration_t, t.time_unit_id, tu.name AS name_tu, tu.abbreviation AS abbreviation_tu,"
                 + " ep.academic_degree_id, ad.name AS name_ad, ad.abbreviation AS abbreviation_ad, ad.ordering AS ordering_ad,"
                 + " ep.educational_level_id, el.name AS name_el, el.ordering AS ordering_el"
                 + " FROM educational_plans AS ep, terms AS t, time_units AS tu, academic_degrees AS ad, educational_levels AS el"
                 + " WHERE t.id = ep.term_id AND t.time_unit_id = tu.id"
                 + " el.id = ep.educational_level_id"
                 + " ad.id = ep.academic_degree_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                TimeUnit tu;
                Term t;
                AcademicDegree ad;
                EducationalLevel el;
                EducationalPlan ep;
                while ( rdr.Read( ) ) {
                    tu = new TimeUnit( ( int ) rdr [ "time_unit_id" ], ( string ) rdr [ "name_tu" ], ( string ) rdr [ "abbreviation_tu" ] );
                    t = new Term( ( int ) rdr [ "term_id" ], ( string ) rdr [ "name_t" ], ( int ) rdr [ "duration_t" ], tu );
                    ad = new AcademicDegree( ( int ) rdr [ "academic_degree_id" ], ( string ) rdr [ "name_ad" ], ( string ) rdr [ "abbreviation_ad" ], ( int ) rdr [ "ordering_ad" ] );
                    el = new EducationalLevel( ( int ) rdr [ "educational_level_id" ], ( string ) rdr [ "name_el" ], ( int ) rdr [ "ordering_el" ] );
                    ep = new EducationalPlan( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ],
                        ( int ) rdr [ "total_credits" ], ( int ) rdr [ "duration_in_terms" ], ( int ) rdr [ "min_duration" ],
                        ( int ) rdr [ "max_duration" ], ( int ) rdr [ "year" ], t, el, ad, ( bool ) rdr [ "active" ] );
                    epl.Add( ep );
                }
                rdr.Close( );
            }
            return epl;
        }
        #endregion

        #endregion
    }
}
